WITH RECURSIVE patches 0818
Hi,
Here is the latest WITH RECURSIVE patches against CVS HEAD. Besides
syncing to CVS HEAD, followings are main differences from previous
one:
1) Enhance sgml docs. Patches contributed by Jeff Davis.
2) Fix bug with plans using HashAggregate. Hash tables should be
recreated if recursive scan is used. Otherwise goes into infinite
recursion.
Also I include an implementation doc slightly enhanced previous one.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
On Mon, Aug 18, 2008 at 04:38:52PM +0900, Tatsuo Ishii wrote:
Hi,
Here is the latest WITH RECURSIVE patches against CVS HEAD. Besides
syncing to CVS HEAD, followings are main differences from previous
one:
Thanks for the new patch :)
I think I may have found another bug:
WITH RECURSIVE t(i,j) AS (
VALUES (1,2)
UNION ALL
SELECT t2.i, t.j
FROM (
SELECT 2 AS i
UNION ALL /* Wrongly getting detected, I think */
SELECT 3 AS i
) AS t2
JOIN
t
ON (t2.i = t.i)
)
SELECT * FROM t;
ERROR: attribute number 2 exceeds number of columns 1
Is there some way to ensure that in the case of WITH RECURSIVE, the
query to the right of UNION ALL follows only the SQL:2008 rules about
not having outer JOINs, etc. in it, but otherwise make it opaque to
the error-checking code?
I know I didn't explain that well, but the above SQL should work and
the error appears to stem from the parser's looking at the innermost
UNION ALL instead of the outermost.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
I think I may have found another bug:
WITH RECURSIVE t(i,j) AS (
VALUES (1,2)
UNION ALL
SELECT t2.i, t.j
FROM (
SELECT 2 AS i
UNION ALL /* Wrongly getting detected, I think */
SELECT 3 AS i
) AS t2
JOIN
t
ON (t2.i = t.i)
)
SELECT * FROM t;
ERROR: attribute number 2 exceeds number of columns 1Is there some way to ensure that in the case of WITH RECURSIVE, the
query to the right of UNION ALL follows only the SQL:2008 rules about
not having outer JOINs, etc. in it, but otherwise make it opaque to
the error-checking code?I know I didn't explain that well, but the above SQL should work and
the error appears to stem from the parser's looking at the innermost
UNION ALL instead of the outermost.
Thanks for the report. I will look into this.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
I think I may have found another bug:
WITH RECURSIVE t(i,j) AS (
VALUES (1,2)
UNION ALL
SELECT t2.i, t.j
FROM (
SELECT 2 AS i
UNION ALL /* Wrongly getting detected, I think */
SELECT 3 AS i
) AS t2
JOIN
t
ON (t2.i = t.i)
)
SELECT * FROM t;
ERROR: attribute number 2 exceeds number of columns 1Is there some way to ensure that in the case of WITH RECURSIVE, the
query to the right of UNION ALL follows only the SQL:2008 rules about
not having outer JOINs, etc. in it, but otherwise make it opaque to
the error-checking code?I know I didn't explain that well, but the above SQL should work and
the error appears to stem from the parser's looking at the innermost
UNION ALL instead of the outermost.
Here is new patches fixing the bug you pointed out (patches was
created by Yoshiyuki). Also I added your SQL to the regression test,
and now the patches is against CVS HEAD. For your convenience I also
include patches against the previous version.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
Attachments:
recursive_query.patch.gzapplication/octet-streamDownload
��I�H recursive_query.patch �<ks�8���_�xv3�E����Wy'Q�cglgsW���� � EjH��wf��_w�A���gw��R3�6��F� poo�-��o��8�G!�?�{�!��G������;�z�W�7�v���}���wju��U����j�*P�N<���6({U���v�}�o4s(����5�V���G���_�'������`����=��#�|���>~\�a/O_�������Xx��;7g#�����'t�<����W7�
[���h��<f-V��>�O0������k������?�������.�Yi���"�Q���!�L�� ���!�Wl���e�X�C�3>)�$5�������M�`>tc>_��"Q7�V+u�n������A��(M��o��������*�A(�1��\'� b�R�o9����%
A������Z��w�)pt[�l����Z����������f\�-�<y��`6�r��"���_
�_of����\��9�����������
����Z�S)�J����L U�h?q$���g�M���V��M,<X�n��eG�H����
����0w�|=XU;��c�����a>�b��|�#��W������:�q���7���,#�u9�";� �]�8K�2���?s</���`���E>r'w����
���3'f#�g7��y����;�2d��-Bw� 4��w�zN��g�Wi�Ng1���'� �n]��&�����bZB���F�����E,��� �,����`��1+��� q.0= ���'��mhL�k�lw#M��bn� ��x$�HP��bx���\�V���^�p�s��`�K,B(~� � �� ��"���EV� �%���9�)$�Q0_x�0Y����p~?G��5�@��V}u=Ob�D���{w�����������������������������K\{,�� �3�����y5���r+�0�'��~�-�_�0�.Y��w-������f�Z���{�/f/���wd�a0��:�,QJ'M������o
�E3T\� � a}�25�( ����TD\
����Ay��(X����ex����W��1�?��b:9����D/����x����_
�l��|yz�nF��Qi"����| Uo����T��*���?��uB�imu�5���l���v[����*k�������� �4��(���,���E���W5
Cw����IQ�����'G KC����0X3��O�4l��r�] ��c��U����.1t_�.+k�Hbo� �����o�����yW��`��/�������<X�.��}�| �� �C:z���If��| �!d�����Iw@�A����p<��B!S�iM�G��/��w /C�s���I��E<�1o�kl�����G�;�\�9@EZ�f����G-�,=�����V\-���H<�p�K���L�2����Zxa��|xszy�x�~F=�Y�-Kc���5E#������_7��P�a5E |�#0 �D���@Nj���\��4?��W����2��M���#Z�t�
�L7�����1 ��h�� ��k����f�f����{[���E��� m��9�V��j5��5t����9�H��'��������h�=�7!w� 7u�K����Sk�Q�����1�/n<w'�A����E�k7�V�e� �Vz������<�� ;�x������r��p����������R����-��m�V��I��mt�nR�����g�c0����[������(n)�5L���T*a�+3PW�\=��Bw��Y�, ��\:��_�x�~�w/��a<�pXkPY����Q�8$�_��A o�����X��n�N�B�����#$co_�
����b�������P[���Z�F�>��L���!���t�`��$(Eqh�]�����9�����e��Pg����X�������90�N��[=;o��o���m�dq.��\h�u'�kD6�C��7��%���1���r��w_�VPR����0��7z�]o6���nBS��^N� p�Mp:QYr�*&�� Qj1=�2)�P{���Tr������W���t���A������'Dgz-0��GZ��������Q�Q{X���!dGL�~W*r
�R_�+ur%@�X�tl�`�iL������5�����gg�SNDFKE�z#G��s&��9F��5��@f�����A�����'�����f���~�{O_�
��]����U:;E~jwZ����^��;T�� ���h}�D�<l�"��vR~N=�p�%���<5��(��`!!��1p]�,�| %_e�\��B!�
B��wA����e�r��]��u,�>WB+� �
�M��X>�(q����h���"dyH~�����i[������7��&o�)��d��~����~��o<�������4�V���G ���#o �eW�R��� �<���PeP�=[@IEZ7�T�u J���^D������1�x�aG�M��� e����TJB+���a2������A�'�8�:���/�8��"7�yV���������pF�m�4BN��3�9KFC��m������"�5Z]0�F��hCC�Xlo���� Z)�m���0����{j�f���5���I�����F\]��]�����������`���o-�o�����k�};?[Y�P)!8Z9iY���%/�G���Zv��A��V����G�B�����z�.F(?�Rm��Y��o������9��w�-���x����{����`�m����h���Ta
:����L��|�4S!M�{LF��@�2��X���e�t�������ek����������6�Q�Y�u�QO��j�������v|�-
PV�{;�������E�"��_�M��=�4�����NAT�K�Y����LG�)�E
������V?����s�7�;z�4��rn�Q��$*3q�v�f���e�7y����}�z���;mXFm��F�
�v��*f�}&R�O\yA)��/�s�[�|��Y�p�T5�f������m�����s�B������gL�j�54���V��F�����;���i�3���?~T�!q>�^d�R6��E.��vr�N��������p.��f�|���a]\��'��Yzjb��i��i�H��E��rA��H<v����/�0�j2r �l�����O���������������K�H����<�`���HL��K�tJ^�,M6��E���B?��U�����j3'JN ���Uc���9x����`����E�2q����3�cC��it���6C:
�O��`����'��0
�4���9$&�`_,�a�w
�>��F3���,��#j���e�P���;}�1��*?`�8��n�.x|<j:��D9�5B�
�TG��{|�!��f�wH��������.8 ����X��'�_��������B���*�xvn��?��
�=�p�
���&�'�4�m����B�xBa��Q��M��s6q����nc;�c>nE� o��hb=��-�(tp����/H|����,�kScI�<�24Y�����n(6}��}��[��#��v�V�X�W��n�����JB�gN����Q]��jF!�K�~�b�N�L"����v���C�4z�I�u{���
2xm�2���pJ�� ���!V7�m����M��-����n���&4$E|��� �t:�0ip��>V�����?���`�����5C6J�8h>���K�����E�xG��q&A<zZRA
KB��'��Q����"�A! $�/�G���2,�`4�T�AZ%a\��-x�2�c�| O�*D�"[�l4�������a��FC*��*����x<�9�����B�����"�D�G�S6FS�C4
�� �M��;�D��M�j��P:4���w@g4��a�i#��f�$�5�E���.L�8\$�S��f�j���f�j%�,�_g�h�a��9����`��kU��� ,�����.��2O6F���[�b:����8��'s���]��PC�������]��I���0\����$�8]����[&+����V�4�#
�)�9m��q}�0�o�l+p��0��A�7��V�{���|O��Z=��<�<s&���C�M��S#+�k1`���A�������m������#��-�lBrHb������<�r1R����8�D��=f�}�-��G������=�r�������2K���l�����S<`�1z>?��� �_n���(����"� s��x8�cJ,�I���:�-n�4�/�UF��+������x���T��]��n2L x�N��R��~�o��L���s&��?/��F��#U��;_z� ���n�Hzc�����}�c�a����p`Va���j#�|o�m��"��� $�W;93�������g�p���.��T������9�`1�%��R�����u}JXc�?������P��+���/��IiX�N������h�>��)P�Y��_�����&��M��*����M�
����d7�z+�?�_���#j��2�>^o���?�xr�@��5�;H%�x-{,����������N�`DOC~��{�7 ��x�4X�R{����w� �?M���3���"��@-B�u������������������_��� ?.��E���b�H>)>���B�����q���X����O��]�s���>F��"K=r�s,�i����uf*�|
�/� ����Hm��>
)JA�����D����'��-^����tQ�Kd*�5x S$p�&z�"w�K.���]^z��1!�1J���5�?W}��� ��/\#�����I�91 4q�F�S�Ov(!�l �&��~���H��&�%��O+aW�?0N^8
�`I�������H'�B�U���kG�!��RP�����(��K�5t�]���9cx������h���^<_��,� w)kS�c����R7Y�d�B�1�cj[�����OjyZ��TH�[hBiu��i���9F�H�C��LR&���T�>����F��8�0*!rqM]�L���b�'