Details

      Description

      I have two SQL statements that is logically identical but MariaDB give different results.
      The first statement produces no result while the second produced one.
      I have test the same data on another DBMS and the results of the two are the same.
      The two SQL statements are
      1.

      select 
      	way_id
      from
      	way_tags_test
      where
      	k = 'highway' and
      	v in (
      		select type from way_types
      	) and
      	way_id in	(
      		select way_id from taxi.way_tags_test where k = 'name'
      	)
      ;

      2.

      select 
      	way_id
      from
      	way_tags_test
      where
      	k = 'name' and
      	way_id in (
      		select
      			way_id
      		from
      			way_tags_test
      		where
      			k='highway' and
      			v in (
      				select type from way_types
      			)
      	)
      ; 

      The table contents are as below:
      way_tags_test have two rows:

      99979604	highway	living_street	2
      99979604	name	九华山	2

      way_types have only one row:

      1	motorway

      DDLs for table are as below:

      CREATE TABLE `way_tags_test` (
        `way_id` bigint(20) NOT NULL,
        `k` varchar(255) DEFAULT NULL,
        `v` varchar(255) DEFAULT NULL,
        `version` bigint(20) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      CREATE TABLE `way_types` (
        `id` int(11) NOT NULL,
        `type` varchar(32) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              TaoXu TaoXu
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: